package com.aaa.book_mall.dao.impl;

import com.aaa.book_mall.dao.OutDao;
import com.aaa.book_mall.entity.Book;
import com.aaa.book_mall.entity.Out;
import com.aaa.book_mall.util.JDBCUtil;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description: 出库接口实现类
 * @ClassName: OutDaoImpl
 * @Author: fengyun
 * @Date: 2021/1/20 14:03
 * @Version: 1.0
 */
@Repository
public class OutDaoImpl implements OutDao {
    @Override
    public int intserOut(Out out) {
        String sql="insert into t_out values(null,?,?,?,?,?,?,?)";
        int i = JDBCUtil.update(sql, out.getOutnumber(),out.getStaffid(),out.getStaffname(),out.getBookcount(),out.getBookprice(),out.getCreatedate(),out.getStatus());
        return i;
    }

    @Override
    public List<Map<String, Object>> queryNoAuditInfo(Out out) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id,outnumber,staffid,staffname,bookcount,bookprice,createdate,status from t_out where status='0'");
        sql.append(" limit ?,?");
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql.toString(), (out.getPage() - 1) * out.getLimit(), out.getLimit());
        System.out.println("querynoauditinfo");
        System.out.println(maps);
        return maps;
    }

    @Override
    public int queryCountInfo() {
        String sql="select count(*) from t_out where status='0'";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    @Override
    public int updateStatus(String status,String outnumber) {
        String sql="update t_out set status=? where outnumber=?";
        int i = JDBCUtil.update(sql,status, outnumber);
        return i;
    }

    @Override
    public List<Map<String, Object>> getAllOutInfo(Out out) {
        System.out.println("getAllOutInfo");
        StringBuilder sql = new StringBuilder();
        sql.append("select * from t_out");
        String startdate="";
        String enddate="";
        System.out.println(".....................");
//        当筛选条件有日期时，对日期进行分割
        if(out.getScopedate()!=null && !"".equals(out.getScopedate())){
            System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$");
            String scopedate = out.getScopedate();
//           截取开始时间
            startdate = scopedate.substring(0, 18);
            System.out.println(startdate);
            enddate = scopedate.substring(21);
            System.out.println(enddate);
        }
        System.out.println("///////////////////////////////////////");
        System.out.println(out.getStaffname());
//          根据条件拼接sql语句
        if(out.getScopedate()!=null && !"".equals(out.getScopedate()) && out.getStaffname()!=null && !"".equals(out.getStaffname())){
            sql.append(" where createdate >'"+startdate+"' and createdate <'"+enddate+"' and staffname like '%"+out.getStaffname()+"%'");
        }else if(out.getScopedate()!=null && !"".equals(out.getScopedate())){
            sql.append(" where createdate >'"+startdate+"' and createdate <'"+enddate+"'");
        }else if(out.getStaffname()!=null && !"".equals(out.getStaffname())){
            sql.append(" where staffname like '%"+out.getStaffname()+"%'");
        }else {
            System.out.println("limit");
            sql.append(" limit ?,?");
        }
        System.out.println(sql);
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql.toString(),(out.getPage() - 1) * out.getLimit(), out.getLimit());
        return maps;
    }

    @Override
    public int queryCountAllInfo() {
        String sql="select count(*) from t_out";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    /**
     * 查询出库数量
     * @param out
     * @return
     */
    @Override
    public List<Map<String, Object>> selectOut(Out out) {
        String sql = "select tb.bookname,sum(tod.bookcount) bookcount from t_out tou INNER JOIN t_outdetail tod ON tou.outnumber=tod.out_outnumber INNER JOIN t_book tb ON tb.bookid=tod.bookid where tou.`status`='1' GROUP BY tb.bookname ORDER BY tod.bookcount desc LIMIT 5";
        List<Map<String, Object>> list = JDBCUtil.queryListMap(sql, out);
        if(list.size()>0){
            return list;
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> selectSellCount() {
        String sql="select sum(td.bookcount) value,trim(tbook.bookname) name from t_out tout inner join t_outdetail td on tout.outnumber=td.out_outnumber inner join t_book tbook on td.bookid=tbook.bookid where tout.`status`=1 GROUP BY td.bookid";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql);
        System.out.println("selectSellCount");
        System.out.println(maps);
        return maps;
    }

    /**
     * 查询出版社销售排行
     * @return
     */
    @Override
    public List<Map<String, Object>> selectPublisher() {
        System.out.println("进入查询出版社销售排行。。");
        String sql = "select trim(tb.publisher) name,sum(tou.bookprice) value from t_out tou INNER JOIN t_outdetail tod ON tou.outnumber=tod.out_outnumber and tou.status='1' INNER JOIN t_book tb  ON tb.bookid=tod.bookid GROUP BY tb.publisher";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql);
        System.out.println(maps);
        return maps;
    }

    /**
     * 查询出入库总金额
     * @param scopDate
     * @return
     */
    @Override
    public List<List> selectOutMoney(String scopDate) {
        String startdate="";
        String enddate="";
        System.out.println(".....................");
        boolean b=false;
//        当筛选条件有日期时，对日期进行分割
        if(scopDate!=null && !"".equals(scopDate)){
            System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$");
//           截取开始时间
            startdate = scopDate.substring(0, 7);
            System.out.println(startdate);
            enddate = scopDate.substring(22,29);
            System.out.println(enddate);
            if(startdate.equals(enddate)){
                b=true;
            }
        }
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT CONCAT(YEAR(createdate),'-',MONTH(createdate)) as ymcreatedate, sum(bookprice) price from t_out where `status`=1");
        if(!"".equals(scopDate) && scopDate!=null){
            sql.append(" and createdate > '"+startdate+"' and createdate < '"+enddate+"'");
        }else if(!"".equals(scopDate) && scopDate!=null && b){
            sql.append(" and createdate and '%"+startdate+"%'");
        }
        sql.append(" GROUP BY ymcreatedate");
        System.out.println(sql);
        List<List> lists = JDBCUtil.queryList(sql.toString());//查询的结果
        System.out.println("outMoney");
        System.out.println(lists);
        return lists;
    }
}
